

***********************************************************************************
* create house price changes by county (Zillow) to be used in builder shock measure
***********************************************************************************
local file_path_in "/tochange/"

** want to do this for both county and zipcode level prices
local X FIPS_CODE zip
local Y fips zip
local Z County Zip

local n : word count `X'
forvalues i=1/`n' {
	local xi : word `i' of `X'
	local yi : word `i' of `Y'
	local zi : word `i' of `Z'

	** loop through county and zip prices
	use "`file_path_in'/ZillowHp`zi'.dta",clear
	display " `xi' `yi' `zi'"

	rename houseprice hpZIL_`yi'
	gen loghpZIL_`yi' = log(hpZIL_`yi')
	label var loghpZIL_`yi' "Log `zi' HP"
	sort `xi' date
	tsset `xi' date
	bysort `xi': gen delta1moZIL_`yi' = ((hpZIL_`yi' - L.hpZIL_`yi')/L.hpZIL_`yi')  
	bysort `xi': gen delta1yrZIL_`yi' = ((hpZIL_`yi' - L12.hpZIL_`yi')/L12.hpZIL_`yi')  /* this is original shock var, but calculated from zillow hp index */

	** want to create change in house prices at county, zip level from 2006 to 2009
	rename date dm
	format dm %10.0g
	
	gen date = dofm(dm)
	format date %d
	gen year0=year(date)

	drop year
	rename year0 year
	drop date
	rename dm date
	format date %tm

	** create variable for log change in house price between 06 and 09
	bysort `xi' year: egen avghpZIL_`yi' = mean(hpZIL_`yi')

	local yearlist 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14
	local shock hp  // rev
	foreach s of local shock {
		foreach t of local yearlist {
			gen avg`s'ZIL`t'_`yi' = avg`s'ZIL_`yi' if year==20`t'
			bysort `xi': egen zil`s'`t'_`yi' = max(avg`s'ZIL`t'_`yi')
		}	
	}	

	foreach t of local yearlist {	
		foreach s of local shock {
			gen `s'lnchg07`t'_`yi' = log(zil`s'`t'_`yi')-log(zil`s'07_`yi')
		}
	}		

	foreach s of local shock {

		gen `s'lnchg0006_`yi' = log(zil`s'06_`yi')-log(zil`s'00_`yi')
		gen `s'lnchg0106_`yi' = log(zil`s'06_`yi')-log(zil`s'01_`yi')
		gen `s'lnchg0105_`yi' = log(zil`s'05_`yi')-log(zil`s'01_`yi')
		gen `s'lnchg0104_`yi' = log(zil`s'04_`yi')-log(zil`s'01_`yi')
		gen `s'lnchg0103_`yi' = log(zil`s'03_`yi')-log(zil`s'01_`yi')
		gen `s'lnchg0102_`yi' = log(zil`s'02_`yi')-log(zil`s'01_`yi')
		gen `s'lnchg0005_`yi' = log(zil`s'05_`yi')-log(zil`s'00_`yi')
		gen `s'lnchg0004_`yi' = log(zil`s'04_`yi')-log(zil`s'00_`yi')
		gen `s'lnchg0003_`yi' = log(zil`s'03_`yi')-log(zil`s'00_`yi')
		gen `s'lnchg0002_`yi' = log(zil`s'02_`yi')-log(zil`s'00_`yi')
		gen `s'lnchg0001_`yi' = log(zil`s'01_`yi')-log(zil`s'00_`yi')
		gen `s'lnchg0406_`yi' = log(zil`s'06_`yi')-log(zil`s'04_`yi')
		gen `s'lnchg0407_`yi' = log(zil`s'07_`yi')-log(zil`s'04_`yi')
		gen `s'lnchg0307_`yi' = log(zil`s'07_`yi')-log(zil`s'03_`yi')
		gen `s'lnchg0306_`yi' = log(zil`s'06_`yi')-log(zil`s'03_`yi')
		gen `s'lnchg0305_`yi' = log(zil`s'05_`yi')-log(zil`s'03_`yi')
		gen `s'lnchg0304_`yi' = log(zil`s'04_`yi')-log(zil`s'03_`yi')
		gen `s'lnchg0206_`yi' = log(zil`s'06_`yi')-log(zil`s'02_`yi')
		gen `s'lnchg0205_`yi' = log(zil`s'05_`yi')-log(zil`s'02_`yi')
		gen `s'lnchg0204_`yi' = log(zil`s'04_`yi')-log(zil`s'02_`yi')
		gen `s'lnchg0203_`yi' = log(zil`s'03_`yi')-log(zil`s'02_`yi')
		gen `s'lnchg0609_`yi' = log(zil`s'09_`yi')-log(zil`s'06_`yi')
		gen `s'lnchg0607_`yi' = log(zil`s'07_`yi')-log(zil`s'06_`yi')
		gen `s'lnchg0608_`yi' = log(zil`s'08_`yi')-log(zil`s'06_`yi')
		gen `s'lnchg0610_`yi' = log(zil`s'10_`yi')-log(zil`s'06_`yi')
		gen `s'lnchg0509_`yi' = log(zil`s'09_`yi')-log(zil`s'05_`yi')
		gen `s'lnchg0508_`yi' = log(zil`s'08_`yi')-log(zil`s'05_`yi')
		gen `s'lnchg0507_`yi' = log(zil`s'07_`yi')-log(zil`s'05_`yi')
		gen `s'lnchg0506_`yi' = log(zil`s'06_`yi')-log(zil`s'05_`yi')
		gen `s'lnchg0809_`yi' = log(zil`s'09_`yi')-log(zil`s'08_`yi')		
		gen `s'lnchg1013_`yi' = log(zil`s'13_`yi')-log(zil`s'10_`yi')
		gen `s'lnchg1114_`yi' = log(zil`s'14_`yi')-log(zil`s'11_`yi')
		gen `s'lnchg0912_`yi' = log(zil`s'12_`yi')-log(zil`s'09_`yi')

	}
	drop avg* zil* 

	drop if mi(date)
	
	if "`xi'"=="zip" {
		tostring zipcode, gen(zip)
	}
	saveold "`file_path_in'/house price index/allHPchgs_`yi'.dta", version(13) replace
}
